﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAO
{
    public class DAORPBangDiemMonHoc:DataProvider
    {
        public static DataTable DSLop()
        {
            DataTable bang;
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "select MaLop,TenLop from Lop";
            OleDbDataAdapter da = new OleDbDataAdapter(chuoilenh, ketNoi);
            DataSet ds = new DataSet();
            da.Fill(ds);
            bang = ds.Tables[0];
            Disconnect();
            return bang;
        }
        public static DataTable DSMonHoc(int malop)
        {
            DataTable bang;
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "SELECT DISTINCT MaMonHoc,TenMonHoc from MonHoc as mh,BangDiemMonHoc as bd where bd.MaLop = @MaLop and mh.MaMonHoc = bd.MaMon";
            OleDbCommand lenh = new OleDbCommand(chuoilenh, ketNoi);
            OleDbParameter value = new OleDbParameter("@MaLop", malop);
            lenh.Parameters.Add(value);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = lenh;
            DataSet ds = new DataSet();
            da.Fill(ds);
            bang = ds.Tables[0];
            Disconnect();
            return bang;
        }
        public static DataTable DSMonHoc(int malop,string hocky)
        {         
            string chuoilenh = "SELECT DISTINCT MaMonHoc,TenMonHoc from MonHoc as mh,BangDiemMonHoc as bd where bd.MaLop = @MaLop and mh.HocKy=@HocKy and mh.MaMonHoc = bd.MaMon";
            OleDbDataAdapter da = new OleDbDataAdapter(chuoilenh,Connect());
            da.SelectCommand.Parameters.AddWithValue("@MaLop",malop);
            da.SelectCommand.Parameters.AddWithValue("@HocKy", hocky);
            new OleDbCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds);            
            Disconnect();
            return ds.Tables[0];
        }
        public static DataTable DSKetQua(int malop, string mamon, int hocky)
        {
            DataTable bangkq;
            OleDbConnection ketNoi = Connect();
            //string ChuoiLenh = "select HoTen,Diem15p,Diem1tiet,DiemCuoiHocKy from HocSinh as hs, BangDiemMonHoc as bd where bd.MaLop=@ma and bd.MaMon=@mon and bd.HocKy=@hk";
            string ChuoiLenh = "select hs.HoTen,bd.Diem15p,bd.Diem1tiet,bd.DiemCuoiHocKy from BangDiemMonHoc as bd, HocSinh as hs where bd.MaLop=@ma and bd.MaLop=hs.MaLop and bd.HocKy=@hk and bd.MaMon=@mon and bd.MSHS=hs.MSHS";
            OleDbCommand lenh = new OleDbCommand(ChuoiLenh, ketNoi);
            OleDbParameter values = new OleDbParameter("@ma", malop);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@hk", hocky);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@mon", mamon);
            lenh.Parameters.Add(values);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = lenh;
            DataSet ds = new DataSet();
            da.Fill(ds);
            bangkq = ds.Tables[0];
            Disconnect();
            return bangkq;
        }
    }
}
